package com.wn.procurement.web;

import com.wn.entity.Procurement;
import com.wn.entity.ShoppingList;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Component
public class ExcelToList {
    //导入Excel数据
    public List<ShoppingList> importExcelAction(String filePath) throws Exception {
        //文件路径
       // Procurement procurement = new Procurement();
       // String filePath = procurement.getShoppingList();
        XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
        XSSFSheet sheet = wookbook.getSheet("Sheet1");
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //遍历行
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        for (int i = 1; i < rows; i++) {
            // 读取左上端单元格
            XSSFRow row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                Map<String, Object> map = new HashMap<String, Object>();
                //获取到Excel文件中的所有的列
                int cells = row.getPhysicalNumberOfCells();
                //材料编号
                XSSFCell id = row.getCell(1);
                String materialId = getValue(id);
                //材料名称
                XSSFCell name = row.getCell(2);
                String materialName = getValue(name);
                //材料数量
                XSSFCell number = row.getCell(3);
                String materialNumber = getValue(number);
                //规格
                XSSFCell spec1 = row.getCell(4);
                String spec = getValue(spec1);
                //价格
                XSSFCell price1 = row.getCell(5);
                String price = getValue(price1);
                //物流订单号
                XSSFCell logistics1 = row.getCell(6);
                String logistics = getValue(logistics1);
                //供应商
                XSSFCell supplier1 = row.getCell(7);
                String supplier = getValue(supplier1);
                //质保证书编号
                XSSFCell qACN1 = row.getCell(8);
                String qACN = getValue(qACN1);
                //到货状态
                XSSFCell goodsState1 = row.getCell(9);
                String goodsState = getValue(goodsState1);
                map.put("materialId", materialId);
                map.put("materialName", materialName);
                map.put("materialNumber", materialNumber);
                map.put("spec", spec);
                map.put("price", price);
                map.put("logistics", logistics);
                map.put("supplier", supplier);
                map.put("qACN", qACN);
                map.put("goodsState", goodsState);
                list.add(map);
            } }
        JSONArray jsonArray = new JSONArray();
        jsonArray.addAll(list);
        //方式一
        List<ShoppingList> shoppingList = jsonArray.toJavaList(ShoppingList.class);
        //方式二
        //List<ShoppingList> shoppingList = JSONObject.parseArray(jsonArray.toJSONString(), ShoppingList.class);
        System.out.println("list = " + shoppingList.toString());
        return  shoppingList;
    }

    private String getValue(XSSFCell xSSFCell) {
        if (null == xSSFCell) {
            return "";
        }
        if (xSSFCell.getCellType() == xSSFCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(xSSFCell.getBooleanCellValue());
        } else if (xSSFCell.getCellType() == xSSFCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(xSSFCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(xSSFCell.getStringCellValue());
        }
    }

}
